package db;

import entities.*;
import java.sql.*;
import java.util.*;

public class DbRelationshipManager {

	static Connection con = null;
	static PreparedStatement getFriendsStmt;
	static PreparedStatement getAntifriendsStmt;
	static PreparedStatement isFriendStmt;
	static PreparedStatement isAntiFriendStmt;
	static PreparedStatement addFriendStmt;
	static PreparedStatement deleteFriendStmt;
	static PreparedStatement deleteAntifriendStmt;
	static PreparedStatement antifriendStmt;
	static PreparedStatement friendStmt;

	private static String getFriendString = "select tbluser.* from tblfriends inner join tbluser on tblfriends.targetID = tbluser.UserID "
			+ " where tblfriends.initiatorID = ?"
			+ " union "
			+ " select tbluser.* from tblfriends inner join tbluser on tblfriends.initiatorID = tbluser.UserID"
			+ " where tblfriends.targetID = ?";
	private static String getAntifriendsString = "select tbluser.* from tblantifriends inner join tbluser on tblantifriends.targID = tbluser.UserID "
			+ " where tblantifriends.initID = ?";
	private static String isFriend = "select * from tblfriends where ((initiatorID = ? and targetID = ?) or (targetID = ? and initiatorID = ?))";
	private static String isAntiFriend = "select * from tblantifriends where (initID = ? and targID = ?)";
	private static String addFriend = "insert into tblfriends(initiatorID,targetID) values (?,?)";
	private static String deleteFriend = "delete from tblfriends where (initiatorID = ? and targetID = ?) or (initiatorID = ? and targetID = ?)";
	private static String makeAntifriend = "insert into tblantifriends(initID,targID) VALUES (?,?)";
	private static String makeFriend = "insert into tblfriends(initiatorID,targetID) VALUES (?,?)";
	private static String deleteAntifriend = "delete from tblantifriends where (initID = ? and targID = ?)";

	private DbRelationshipManager() {

	}

	private static void getConnection() throws SQLException {
		if (!Connector.isValidConnection(con)) {
			con = Connector.getConnection();
			getFriendsStmt = con.prepareStatement(getFriendString);
			getAntifriendsStmt = con.prepareStatement(getAntifriendsString);
			isFriendStmt = con.prepareStatement(isFriend);
			isAntiFriendStmt = con.prepareStatement(isAntiFriend);
			addFriendStmt = con.prepareStatement(addFriend);
			deleteFriendStmt = con.prepareStatement(deleteFriend);
			antifriendStmt = con.prepareStatement(makeAntifriend);
			deleteAntifriendStmt = con.prepareStatement(deleteAntifriend);
			friendStmt = con.prepareStatement(makeFriend);
			if (!Connector.isValidConnection(con)) {
				throw new DBError("Cannot Create DB Connection");

			}
		}
	}

	public static boolean friend(int userId, int searchId) {
		try {
			getConnection();

			isFriendStmt.clearParameters();
			isFriendStmt.setInt(1, searchId);
			isFriendStmt.setInt(2, userId);
			isFriendStmt.setInt(3, searchId);
			isFriendStmt.setInt(4, userId);
			ResultSet rs = isFriendStmt.executeQuery();
			if (rs.next())
				return true;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return false;
	}

/*	public static ArrayList<Integer> addFriend(Integer[] id, int userId) {
		ArrayList<Integer> failedId = new ArrayList<Integer>();
		try {
			getConnection();
			addFriendStmt.clearParameters();
			addFriendStmt.setInt(1, userId);

			for (int i = 1; i < id.length; i++) {
				if (!(friend(userId, id[i].intValue())
						|| antifriend(userId, id[i].intValue()))) {
					addFriendStmt.setInt(2, id[i]);

					int j = addFriendStmt.executeUpdate();
					if (j != 1) {
						failedId.add(id[i]);
					}
				}
			}

		} catch (SQLException e) {
			return null;
		}
		return failedId;
	}

	public static ArrayList<Integer> addAntifriend(Integer[] id, int userId) {
		ArrayList<Integer> failedId = new ArrayList<Integer>();
		try {
			getConnection();
			addAntifriendStmt.clearParameters();
			addAntifriendStmt.setInt(1, userId);

			for (int i = 1; i < id.length; i++) {
				if (!(friend(userId, id[i].intValue())
						|| antifriend(userId, id[i].intValue()))) {
					addAntifriendStmt.setInt(2, id[i]);

					int j = addAntifriendStmt.executeUpdate();
					if (j != 1) {
						failedId.add(id[i]);
					}
				}
			}

		} catch (SQLException e) {
			return null;
		}
		return failedId;
	}
*/
	public static boolean antifriend(int userId, int searchId) {
		try {
			getConnection();
			isAntiFriendStmt.clearParameters();
			isAntiFriendStmt.setInt(1, userId);
			isAntiFriendStmt.setInt(2, searchId);
			ResultSet rs = isAntiFriendStmt.executeQuery();
			if (rs.next())
				return true;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return false;
	}

	public static List<User> getFriends(int id) {
		// setup statement
		List<User> friends = new ArrayList<User>();
		try {
			getConnection();
			getFriendsStmt.clearParameters();
			getFriendsStmt.setInt(1, id);
			getFriendsStmt.setInt(2, id);
			ResultSet rs = getFriendsStmt.executeQuery();
			while (rs.next()) {
				User user = new User();

				user.setUserType(rs.getString("userType"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setUserName(rs.getString("emailAddress"));
				user.setPhoneNumber(rs.getString("phoneNumber"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("userId"));
				System.out.println(rs.getString("firstName"));
				friends.add(user);
			}
			return friends;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e);
			return null;
		}

	}

	public static List<User> getAntifriends(int id) {
		// setup statement
		List<User> antifriends = new ArrayList<User>();
		try {
			getConnection();
			getAntifriendsStmt.clearParameters();
			getAntifriendsStmt.setInt(1, id);
			ResultSet rs = getAntifriendsStmt.executeQuery();
			while (rs.next()) {
				User user = new User();

				user.setUserType(rs.getString("userType"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setUserName(rs.getString("emailAddress"));
				user.setPhoneNumber(rs.getString("phoneNumber"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("userId"));
				System.out.println(rs.getString("firstName"));
				antifriends.add(user);
			}
			return antifriends;
		} catch (SQLException e) {
			System.out.println(e);
			// TODO Auto-generated catch block
			return null;
		}

	}

	public static boolean deleteFriend(int userId, int delFriendId) {

		try {
			getConnection();
			deleteFriendStmt.setInt(1, userId);
			deleteFriendStmt.setInt(2, delFriendId);
			deleteFriendStmt.setInt(3, delFriendId);
			deleteFriendStmt.setInt(4, userId);
			int result = deleteFriendStmt.executeUpdate();
			if (result != 1) {
				return false;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return true;
	}

	public static boolean deleteAntifriend(int userId, int delFriendId) {

		try {
			getConnection();
			deleteAntifriendStmt.setInt(1, userId);
			deleteAntifriendStmt.setInt(2, delFriendId);
			int result = deleteAntifriendStmt.executeUpdate();
			if (result != 1) {
				return false;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return true;
	}

	public static boolean makeAntifriend(int userId, int intValue) {
		// TODO Auto-generated method stub
		boolean isDel = true;
		if ( antifriend(intValue, userId) || antifriend(userId, intValue) ) return true;
		if ( friend(userId, intValue) ) {
			isDel = deleteFriend(userId, intValue);
		}

		if ( isDel == false ) return false;
		try {
			getConnection();
			antifriendStmt.setInt(1, userId);
			antifriendStmt.setInt(2, intValue);
			int rs = antifriendStmt.executeUpdate();
			if (rs != 1)
				return false;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return true;
	}

	public static boolean makeFriend(int userId, int intValue) {
		// TODO Auto-generated method stub
		boolean isDel = true;
		
		if ( antifriend(intValue, userId) || friend(userId,intValue) ) {
			return false;
		}
		if ( antifriend(userId, intValue) ) {
			isDel = deleteAntifriend(userId, intValue);
		}

		if ( isDel = false ) return false;
		try {
			getConnection();
			friendStmt.setInt(1, userId);
			friendStmt.setInt(2, intValue);
			int rs = friendStmt.executeUpdate();
			if (rs != 1)
				return false;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
		return true;
	}

}